package common.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;


import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.dao.JdbcDAO;
import common.pojo.Member;
import common.utils.MD5Util;

@Scope("prototype")
@Repository
public class MemberDAO extends JdbcDAO {
		
	@SuppressWarnings("unchecked")
	public List<Member> getListByPage(int start, int limit, String orderby,
			String desc) {
		class Mapper implements RowMapper {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Member member = new Member();
				member.setId(rs.getInt(1));
				member.setName(rs.getString(2));
				member.setEmail(rs.getString(3));
				member.setRegTime(rs.getTimestamp(4));
				member.setLastLoginTime(rs.getTimestamp(5));
				return member;
			}
		}
		String sql = "SELECT * FROM("
				+ "select id,name,email,regTime,lastLoginTime , ROW_NUMBER() OVER (ORDER BY "+orderby+" "+desc+") as rowNum " 
				+"FROM member JOIN memberOtherInfo ON member.id = memberOtherInfo.memberID  "
				+ ") AS T WHERE T.RowNum>"+start+" and T.RowNum<="+(start+limit);
		return getList(sql,null,new Mapper());
	}
	/**
	 * 取得数据总数
	 * 
	 * @return
	 */
	public int getCount() {
		return getCount("select count(*) from member",null);
	}

	/**
	 * 根据id操作
	 * 
	 * @param id
	 * @return
	 */
	public Member findInfoById(int id) {
		String sql = "select * from member where id ="+ id;
 		return (Member) getObject(sql,null, new MembersMapper());
	}

	public Member findMember(String where)
	{
		String sql="select * from member where "+where;
		return (Member) getObject(sql, null, new MembersMapper());
	}
	
	/**
	 * 更新数据库
	 * 
	 * @param 
	 * @return
	 */
	public boolean update(Member member, String repwd) {
		String sql = "";
		if(!repwd.equals("")){
			sql = "update member set password=?, email=?, state=? where id =?";
			update(sql, new Object[] { MD5Util.md5(member.getPassword()), member.getEmail(),
					member.getState(), member.getId()});
		}else {
			sql = "update member set  email=?, state=? where id =?";
			update(sql, new Object[] { member.getEmail(),
					 member.getState(), member.getId()});
		}
		return true;
	}

	private static final class MembersMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Member member = new Member();
			member.setId(rs.getInt(1));
			member.setName(rs.getString(2));
			member.setPassword(rs.getString(3));
			member.setEmail(rs.getString(4));
			member.setState(rs.getBoolean(5));
		return member;
		}
	}

	public boolean delete(int id){
		String sql ="delete from member where id = "+id;
		delete(sql);
		return true;
	}
	
	
	//保存，并返回id
	public int save(final Member member)
	{
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			String sql = "insert into member(name,email,password,state) values(?,?,?,?)";
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, member.getName());
				ps.setString(2, member.getEmail());
				ps.setString(3, MD5Util.md5(member.getPassword()));
				ps.setBoolean(4, member.getState());
				return ps;
			}
		};
		KeyHolder key =  saveandgetkey(psc);
		return key.getKey().intValue();
	}
	public Member findInfoByName(String name) {
		String sql = "select * from member where name ='"+ name+"'";
 		return (Member) getObject(sql,null, new MembersMapper());
	}
	
	public void update(Member member) {
		String sql = "update member set email=?  where id =?";
		update(sql, new Object[]{member.getEmail(),member.getId()});
	}
	
	public void update(String pwd, int memberId) {
		String sql = "update member set password=? where id =?";
		update(sql, new Object[]{pwd,memberId});
		
	}
}
